#include "mysqlconnctor.h"

#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>

MySqlConnctor::MySqlConnctor(QString dbname,QString hn,QString u,QString p):DataBase(dbname,hn,u,p)
{

}

void MySqlConnctor::insertTo_device_defect_info(QList<device_defect_info> list,const QSqlDatabase &db)
{
    QString sqlstr = R"(
    INSERT INTO tb_device_defect_info(Device_ID,Defect_ID,Defect_Name,Defect_Color)
    VALUES (:deviceId, :defectId, :defectName,:dColor);
    )";

    QString sqlstr_checkDefect =R"(
    SELECT * FROM tb_device_defect_info
    WHERE
    device_ID = :id AND
    defect_ID = :defectId
    AND defect_Name = :dName;
    )";


    QSqlQuery query(db);

    foreach (const device_defect_info &item, list) {
        query.prepare(sqlstr_checkDefect);
        query.bindValue(":id",item.Device_ID);
        query.bindValue(":defectId",item.Defect_ID);
        query.bindValue(":dName",item.Defect_Name);
        query.exec();
        if(query.next()){
            return;
        }
        else{
            query.prepare(sqlstr);
            query.bindValue(":deviceId",item.Device_ID);
            query.bindValue(":defectId",item.Defect_ID);
            query.bindValue(":defectName",item.Defect_Name);
            query.bindValue(":dColor",item.Defect_Color);
            if(!query.exec()){
                qDebug() << "插入数据 缺陷类别 失败:" << query.lastError().text();
            }else{
                qDebug() << "缺陷类别插入成功!";
            }
        }

    }

}

void MySqlConnctor::insertTo_device_steel_type_info(const device_steel_type_info &steelTypeInfo,const QSqlDatabase &db)
{
    QString sqlStr = R"(
    INSERT INTO tb_device_steel_type_info(Device_ID,Steel_Type)
    VALUES(:deviceId,:type);
    )";

    QString sqlstr_check = R"(
    SELECT * from tb_device_steel_type_info
    where Device_ID = :deviceid
    and Steel_Type = :type
)";


    QSqlQuery query(db);

    query.prepare(sqlstr_check);
    query.bindValue(":deviceid",steelTypeInfo.Device_ID);
    query.bindValue(":type",steelTypeInfo.Steel_Type);
    query.exec();
    QString DId;
    while (query.next()) {
        int type = query.record().value("Steel_Type").toInt();
        DId = query.record().value("Device_ID").toString();
    }
    if(DId.length()<=0|| DId.isEmpty()){
        query.prepare(sqlStr);
        query.bindValue(":deviceId",steelTypeInfo.Device_ID);
        query.bindValue(":type",steelTypeInfo.Steel_Type);
        if(!query.exec()){
            qDebug() << "插入钢种数据失败:" << query.lastError().text();
        }else{
            qDebug() << "钢种数据插入成功!";
        }
    }


}

void MySqlConnctor::insertTo_device_steel_info(const device_steel_info &steelInfo,const QSqlDatabase &db)
{
    QString sqlstr = R"(
    INSERT INTO tb_device_steel_info(
    Device_ID,Steel_ID,Detect_Time,Steel_Parent_ID,Steel_Type,Grade_ID,Grade_Desc,Steel_Length_Source,
    Steel_Width_Source,Steel_Thick_Source,Steel_Length_Detect,Steel_Width_Detect,Steel_Thick_Detect,Defect_Count_Number,SeqNo
    )
    VALUES(:deviceId,:SteelId,:detectTime,:SteelParent,:SteelType,:Grade,:GradeDesc,:Ls,:Ws,:Ts,:Ld,:Wd,:Td,:DCount,:SeqNo);
    )";
    QSqlQuery query(db);
    query.prepare(sqlstr);
    query.bindValue(":deviceId",steelInfo.Device_ID);
    query.bindValue(":SteelId",steelInfo.Steel_ID);
    query.bindValue(":SteelParent",steelInfo.Steel_Parent_ID);
    query.bindValue(":SteelType",steelInfo.Steel_Type);
    query.bindValue(":detectTime",steelInfo.Detect_Time);
    query.bindValue(":Grade",steelInfo.Grade_ID);
    query.bindValue(":GradeDesc",steelInfo.Grade_Desc);
    query.bindValue(":DCount",steelInfo.Defect_Count_Number);
    query.bindValue(":Ld",steelInfo.Steel_Length_Detect);
    query.bindValue(":Td",steelInfo.Steel_Thick_Detect);
    query.bindValue(":Wd",steelInfo.Steel_Width_Detect);
    query.bindValue(":Ts",steelInfo.Steel_Thick_Source);
    query.bindValue(":Ws",steelInfo.Steel_Width_Source);
    query.bindValue(":Ls",steelInfo.Steel_Length_Source);
    query.bindValue(":SeqNo",steelInfo.sequence);
    if(!query.exec()){
        qDebug() << "插入钢板数据失败:" << query.lastError().text();
    }else{
        qDebug() << "钢板数据插入成功!";
    }
}

void MySqlConnctor::insertTo_device_detect_detail(const QList<device_detect_detail> &defectList, bool Surface,const QSqlDatabase &db)
{
    QString str = R"(
    Insert Into tb_device_detect_detail_:IsTop(Device_ID,Steel_ID,Detect_Time,Camera_ID,Defect_ID,Defect_Image_Name,Defect_Min_Image_Name,
Left_In_Max_Image,Right_In_Max_Image,Top_In_Max_Image,Bottom_In_Max_Image,
Left_In_Steel,Right_In_Steel,Top_In_Steel,Bottom_In_Steel,Defect_Area,Defect_no)
    VALUES(:dId,:steelId,:defectTime,:CId,:DefectID,:Defect_Image_Name,:imgName,:Li,:Ri,:Ti,:Bi,:Ls,:Rs,:Ts,:Bs,:Area,:dNo);
    )";
    QSqlQuery query(db);
    foreach (auto element, defectList) {
        query.prepare(str);
        query.bindValue(":IsTop",Surface?1:2);
        query.bindValue(":dId",element.Device_ID);
        query.bindValue(":steelId",element.Steel_ID);
        query.bindValue(":defectTime",element.Detect_Time);
        query.bindValue(":CId",element.Camera_ID);
        query.bindValue(":DefectID",element.Defect_ID);
        query.bindValue(":Defect_Image_Name",element.Defect_Image_Name);
        query.bindValue(":imgName",element.Defect_Min_Image_Name);
        query.bindValue(":Li",element.Left_In_Max_Image);
        query.bindValue(":Ri",element.Right_In_Max_Image);
        query.bindValue(":Ti",element.Top_In_Max_Image);
        query.bindValue(":Bi",element.Bottom_In_Max_Image);
        query.bindValue(":Ls",element.Left_In_Steel);
        query.bindValue(":Rs",element.Right_In_Steel);
        query.bindValue(":Ts",element.Top_In_Steel);
        query.bindValue(":Bs",element.Bottom_In_Steel);
        query.bindValue(":Area",element.Defect_Area);
        query.bindValue(":dNo",element.Defect_no);
        if(!query.exec()){
            query.lastError().text();
        }
    }

}

QSqlDatabase MySqlConnctor::openDataBase(QString connectionName, QString Table)
{
    if (true == QSqlDatabase::contains(connectionName))
    {
        db = QSqlDatabase::database(connectionName); //存在就使用这个连接
    }else {
        db = QSqlDatabase::addDatabase(SqlDatabaseName, connectionName);
        db.setHostName(hostName);
        db.setUserName(User);
        db.setPassword(Pwd);
        db.setDatabaseName(Table);
        if(!db.open()){
            qDebug()<<"打开<"<< SqlDatabaseName <<">数据库"<<Table<<"失败!";
            qDebug()<<db.lastError().text();
        }
    }
    return db;
}






